import java.sql.*;
import java.util.ArrayList;
import java.util.TreeSet;



public class Connect{
	static Statement stat;

	public static void basic(int caseNum, String[] vals) {	
		ResultSet result;
		String query="";

		//basic query1
		//search by author name
		if(caseNum==1){
			query = "SELECT * FROM publication, pub_auth, author WHERE publication.pub_id=pub_auth.pub_id AND " +
			"pub_auth.auth_id=author.auth_id AND author.name LIKE'%" + vals[0] +"%'";
		}
		//search by publication title
		else if(caseNum==2){
			query = "SELECT * FROM publication WHERE publication.title LIKE'%"+vals[0]+"%'";
		}
		//search by journal name+volume+page numbers (accounts for null values)
		else if (caseNum==3){ 
			query = "SELECT * FROM journal, publication, pub_jour WHERE " +
			"publication.pub_id=pub_jour.pub_id AND " +
			"pub_jour.jour_id=journal.jour_id AND " +
			"journal.name LIKE '%"+vals[0]+"%' AND journal.volume="+vals[1]+
			" AND (publication.start_page IS NULL OR publication.start_page>='"+vals[2]+"') " +
			"AND (publication.end_page IS NULL OR publication.end_page<='"+vals[3]+"')";
		}

		//basic query 2
		//search based on tag
		else if(caseNum==4){
			query = "SELECT * FROM publication, tags, pub_tags WHERE publication.pub_id=pub_tags.pub_id AND " +
			"pub_tags.tag_id=tags.tag_id AND tags.tag LIKE '%" + vals[0] +"%'";
		}
		//search based on tag AND year (default values for empty strings 0 and 10000)
		else if(caseNum==5){
			query = "SELECT * FROM publication, tags, pub_tags WHERE publication.pub_id=pub_tags.pub_id AND " +
			"pub_tags.tag_id=tags.tag_id AND tags.tag LIKE '%"+vals[0]+"%' " +
			"AND publication.year>="+(vals[1].equals("")?0:vals[1]) + " AND publication.year<="+(vals[2].equals("")?10000:vals[2]);
		}

		System.out.println("query -> " + query);
		processResults (query);
	}


	public static void advance(int caseNum, String[] vals){
		String query="";
		ResultSet result;

		//advanced query 1:list of co-authors
		if(caseNum==1){
			query = "SELECT DISTINCT author.auth_id, author.name FROM author, pub_auth WHERE " +
			"pub_auth.auth_id=author.auth_id AND author.name NOT LIKE '%"+vals[0]+"%' AND pub_id IN " +
			"(SELECT DISTINCT pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+vals[0]+"%')";
		}
		//advanced query 2: retreive list of publications by a given pair of authors
		else if(caseNum==2){
			query = "SELECT * FROM publication WHERE publication.pub_id IN " +
			"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+vals[0]+"%') " +
			" AND publication.pub_id IN " +
			"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+vals[1]+"%') ";
		}

		//advanced query 3: retrieve list of co-authors who authored papers w/ either one of the pair of authors
		else if(caseNum==3){
			query = "SELECT DISTINCT author.auth_id, author.name FROM author, pub_auth WHERE " +
			"pub_auth.auth_id=author.auth_id AND " +
			"(author.name NOT LIKE '%"+vals[0]+"%' AND author.name NOT LIKE '%"+vals[1]+"%') AND pub_id IN " +
			"(SELECT DISTINCT pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id " +
			"AND (author.name LIKE '%"+vals[0]+"%' OR author.name LIKE '%"+vals[1]+"%'))";
		}else if(caseNum==4){
			
			query = "SELECT publication.pub_id, publication.title FROM publication, author, pub_auth, " +
					"(SELECT pub_id, MAX(pub_auth.order) as maximum FROM pub_auth GROUP BY pub_id) as max_table " +
					"WHERE publication.pub_id=pub_auth.pub_id AND pub_auth.auth_id=author.auth_id " +
					"AND max_table.pub_id=pub_auth.pub_id AND max_table.maximum=pub_auth.order AND author.name LIKE '%"+vals[0]+"%'";
			
		}

		System.out.println("query -> " + query);
		//query = "SELECT * FROM user";
		processResults (query);
	}


	public static ResultSet processResults(String query){
		ResultSet result;
		try{
			result = stat.executeQuery(query);
			ResultSetMetaData table = result.getMetaData();
			int numberOfColumns = table.getColumnCount();	

			System.out.println("Result(s): "+ table.getTableName(1));

			while(result.next()){
				for (int i = 1; i < numberOfColumns + 1; i++) {
					String columnName = table.getColumnName(i);
					// Get the name of the column's table name
					System.out.print("["+columnName + "->" + result.getString(columnName)+"] ");
				}
				System.out.println();
			}	
		}
		catch (SQLException e){
			System.err.println("no record found");
		}

		return null;
	}


	//helper fctn for advanced query 4 - takes author names as arguments
	public static double A_and_or_B (String a1, String a2){
		String[] query = new String[2];
		ResultSet result;
		Double returnVal = 0.0;

		query [0]= "SELECT count(*) as value FROM publication WHERE publication.pub_id IN " +
		"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+a1+"%') " +
		" AND publication.pub_id IN " +
		"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+a2+"%') ";

		query[1] = "SELECT count(*) as value FROM publication WHERE publication.pub_id IN " +
		"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+a1+"%') " +
		" OR publication.pub_id IN " +
		"(SELECT DISTINCT pub_auth.pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+a2+"%') ";
		try{
			result = stat.executeQuery(query[0]);
			while(result.next()){
				//System.out.println ("[val AND ->" + result.getString("value") + "]");
				returnVal = new Double (result.getString("value"));
				break;
			}

			result = stat.executeQuery(query[1]);
			while(result.next()){
				//System.out.println ("[val OR ->" + result.getString("value") + "]");
				returnVal /= new Double (result.getString("value"));
				break;
			}

			return (double) returnVal.doubleValue();
		}
		catch (SQLException e){
			//System.err.println("no record found");
			return 0;
		}
	}

	//advanced query 4 - computes interaction strength for all authors
	public static ArrayList<ArrayList<String>> interactionStrength () {
		String query="";
		ResultSet result;
		ArrayList<String> arrayOfAuthors = new ArrayList<String>();
		ArrayList<ArrayList<String>> returnVal = new ArrayList<ArrayList<String>>();

		//find all possible authors 
		query = "SELECT author.name FROM author";

		//add all authors to an arraylist
		try{
			result = stat.executeQuery(query);
			while(result.next()){
				arrayOfAuthors.add(result.getString("name"));
			}	
			System.out.println("list of authors "+ arrayOfAuthors);
		}
		catch (SQLException e){
			System.err.println("no record found");
		}

		Double d;
		ArrayList<String> temp;
		TreeSet<String> processedAuthors = new TreeSet<String>();
		//System.out.println((double) 1/6);
		//System.out.println("interaction strength -> " +(double) A_and_or_B("dana", "samir"));

		arrayOfAuthors.remove(0);

		//cartisian product of authors -- a result looks like {a1, a2, interactionStrength}
		for (String s1 : arrayOfAuthors){
			for(String s2: arrayOfAuthors){
				if(!s1.equals(s2) && !processedAuthors.contains(s2)){
					d = A_and_or_B (s1, s2);
					temp = new ArrayList<String>();
					temp.add(s1); temp.add(s2); temp.add(d.toString());
					//System.out.println("temp is" + temp);
					returnVal.add(temp);
				}
			}
			processedAuthors.add(s1);
			break;
		}
		return returnVal;
	}

	
	//advanced query 5, computes distance b/w author a1 and author a2 
	public static int distance (String a1, String a2){
		int distance = 0;
		String query="";
		ResultSet result;
		ArrayList<ArrayList<String>> arrayOfAuthors = new ArrayList<ArrayList<String>>();
		ArrayList<String> currList, tempList=null, processedAuthors = new ArrayList<String>();

		currList = new ArrayList<String>(); 
		currList.add(a1);
		arrayOfAuthors.add(currList);
		String curr;

		//bfs - breadth first search 
		while (arrayOfAuthors.isEmpty()==false){
			
			//list of co-authors at every level
			currList = arrayOfAuthors.remove(0);
			tempList = new ArrayList<String>();
			
			while (currList.isEmpty()==false){
				curr = currList.remove(0);
				
				if(processedAuthors.contains(curr))
					continue;
				
				try{
					//query for co-authors
					query = "SELECT DISTINCT author.auth_id, author.name FROM author, pub_auth WHERE " +
					"pub_auth.auth_id=author.auth_id AND author.name NOT LIKE '%"+curr+"%' AND pub_id IN " +
					"(SELECT DISTINCT pub_id FROM pub_auth, author WHERE pub_auth.auth_id=author.auth_id AND author.name LIKE '%"+curr+"%')";		
					result = stat.executeQuery(query);	

					//authors whose co-authors have been found
					processedAuthors.add(curr);
									
					while(result.next()){
						if(a2.equals(result.getString("author.name")))
							return distance;
						if(tempList.contains(result.getString("author.name"))==false && 
								processedAuthors.contains(result.getString("author.name"))==false)
							tempList.add(result.getString("author.name"));
					}
				}
				catch (SQLException e){}
			}
			
			if(tempList.isEmpty()==false){
				System.out.println("list of co-authors " + tempList + ", distance" + distance);
				arrayOfAuthors.add(tempList);
			}
			distance++;
		}
		//distance is -1 when no path from a1 to a2
		distance = -1;
		System.out.println("distance b/w "+ a1 + " and "+ a2 +" is "+distance);
		return distance;
	}
	


	public static void main (String[] args){
		Connection conn = null;

		try{
			String userName = "root";
			String password = "";
			String url = "jdbc:mysql://localhost/bib_db";
			Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			conn = DriverManager.getConnection (url, userName, password);
			System.out.println ("Database connection established");
			stat = conn.createStatement();

			String[] v1 = {"dana!"};
			//basic(1, v1);

			String[] v2 = {"dana"};
			//basic(2, v2);

			String[] v3 = {"Subrahmanian", "dana", "1", "3"};
			//basic(5, v3);

			System.out.println ("3:");
			advance(4, v3); 

			System.out.println("distance is " + distance ("dana", "Yung-Chun (Justin) Wan"));
			//function takes forever
			//ArrayList<ArrayList<String>> results = interactionStrength();
			//for(ArrayList<String> list : results)System.out.println(list);
			conn.close();
		}
		catch (Exception e)
		{
			System.err.println ("Cannot connect to database server");
		}
		finally
		{
			if (conn != null){
				try{
					conn.close ();
					System.out.println ("Database connection terminated");
				}
				catch (Exception e) { /* ignore close errors */ }
			}
		}
	}
}
